#!/usr/bin/env node

const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const BACKUP_PATH = path.join(process.cwd(), 'sql', 'backup', 'full-backup.sql');

console.log('🔄 开始数据库恢复...\n');

try {
  // 读取备份SQL文件
  console.log('1. 读取备份文件...');
  const backupSQL = fs.readFileSync(BACKUP_PATH, 'utf8');
  console.log(`   备份文件大小: ${(backupSQL.length / 1024).toFixed(1)}KB`);
  
  // 打开数据库
  console.log('2. 连接数据库...');
  const db = new Database(DB_PATH);
  
  // 获取当前表数量
  const currentTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name").all();
  console.log(`   当前表数量: ${currentTables.length}`);
  
  // 执行恢复
  console.log('3. 执行数据库恢复...');
  
  // 分割SQL语句
  const statements = backupSQL
    .split(';')
    .map(stmt => stmt.trim())
    .filter(stmt => stmt.length > 0 && !stmt.startsWith('--'));
  
  console.log(`   总SQL语句数: ${statements.length}`);
  
  let restoredTables = 0;
  let errors = 0;
  
  // 开始事务
  const transaction = db.transaction(() => {
    statements.forEach((statement, index) => {
      try {
        if (statement.toLowerCase().includes('create table')) {
          // 提取表名
          const tableMatch = statement.match(/CREATE TABLE (?:\[)?(\w+)(?:\])?/i);
          const tableName = tableMatch ? tableMatch[1] : 'unknown';
          
          // 检查表是否已存在
          const exists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name = ?").get(tableName);
          
          if (!exists) {
            db.exec(statement);
            restoredTables++;
            console.log(`   恢复表: ${tableName}`);
          } else {
            console.log(`   跳过已存在的表: ${tableName}`);
          }
        } else if (statement.toLowerCase().includes('insert into')) {
          // 对于INSERT语句，尝试执行，如果失败就跳过
          try {
            db.exec(statement);
          } catch (insertError) {
            // INSERT失败通常是因为数据已存在，可以忽略
          }
        } else if (statement.toLowerCase().includes('pragma') || statement.toLowerCase().includes('begin') || statement.toLowerCase().includes('commit')) {
          // 执行PRAGMA和事务控制语句
          db.exec(statement);
        }
      } catch (error) {
        errors++;
        if (errors < 10) { // 只显示前10个错误
          console.log(`   错误 (语句 ${index}): ${error.message.substring(0, 100)}`);
        }
      }
    });
  });
  
  transaction();
  
  // 获取恢复后的表数量
  const finalTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name").all();
  
  console.log('\n4. 恢复完成!');
  console.log(`   恢复的新表数量: ${restoredTables}`);
  console.log(`   总表数量: ${currentTables.length} → ${finalTables.length}`);
  console.log(`   错误数量: ${errors}`);
  
  // 检查特定的表是否恢复
  const deviceInstanceTables = finalTables.filter(t => t.name.startsWith('device_instances_'));
  const parameterTables = finalTables.filter(t => t.name.startsWith('table_c'));
  
  console.log('\n5. 恢复验证:');
  console.log(`   设备实例表: ${deviceInstanceTables.length}个`);
  console.log(`   参数表: ${parameterTables.length}个`);
  
  if (deviceInstanceTables.length > 0) {
    console.log('   设备实例表样例:', deviceInstanceTables.slice(0, 3).map(t => t.name).join(', '));
  }
  
  if (parameterTables.length > 0) {
    console.log('   参数表样例:', parameterTables.slice(0, 3).map(t => t.name).join(', '));
  }
  
  db.close();
  
  console.log('\n✅ 数据库恢复成功!');
  
} catch (error) {
  console.error('❌ 恢复过程中发生错误:', error.message);
  process.exit(1);
}